package cn.hpclub.server.model;

import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;

@TableBind(tableName = "sys_code_value")
public class SysCodeValue extends Model<SysCodeValue>{

    /**
     * 
     */
    private static final long        serialVersionUID = -8671197777331921317L;

    public static final SysCodeValue dao              = new SysCodeValue();

    public final static String       CODE             = "code";
    public final static String       VALUE            = "value";
    public final static String       NAME             = "name";
    public final static String       REMARK           = "remark";
    public final static String       SORT             = "sort";

    public String getSelect(){
        String select = " select t.name as tname,t.code as tcode,t.value as tvalue,"
                + " (select user_name from sys_user u where v.created_by = u.id) as creator, "
                + " (select user_name from sys_user u where v.last_updated_by = u.id) as updator, " + " v.* ";

        return select;
    }

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from sys_code_value as v, sys_code_type as t where v.type_id = t.id ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " order by t.name asc, v.sort asc,v.id asc ";

        return sqlExceptSelect;
    }


    public Record getCodeValueByValueCode(String code){
        String sql = " select v.code, v.value, v.name, v.sort, v.remark "
                + " from sys_code_type as t, sys_code_value as v "
                + " where t.is_deleted = 0 and v.is_deleted = 0 "
                + " and t.id = v.type_id and v.code = ? "
                + " order by v.sort asc, v.id asc ";
        Record record = Db.findFirst(sql, code);
        return record;
    }

    public List<Record> getCodeValuesByValueCodes(String... codes){

        String par = "(";
        for (String code : codes) {
            par+="'"+code+"',";
        }
        if(par.endsWith(",")){
            par = par.substring(0,par.length()-1)+")";
        }

        String sql = " select v.code, v.value, v.name, v.sort, v.remark "
                + " from sys_code_type as t, sys_code_value as v "
                + " where t.is_deleted = 0 and v.is_deleted = 0 "
                + " and t.id = v.type_id and v.code in "+par
                + " order by v.sort asc, v.id asc ";

        List<Record> records = Db.find(sql);
        return records;
    }



    private String buildQuery(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("vname")){
            sql += " and v.name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("tid") && !"0".equals(item.getValue())){
            sql += " and v.type_id = " + item.getValue();
        } else if(item.getKey().equals("vcode")){
            sql += " and v.code like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("vvalue")){
            sql += " and v.value like '%" + item.getValue() + "%' ";
        }else if(item.getKey().equals("remark")){
            sql += " and v.remark like '%" + item.getValue() + "%' ";
        }else if(item.getKey().equals("is_deleted") && !"-1".equals(item.getValue())){
            sql += " and t.is_deleted = " + item.getValue();
        }

        return sql;
    }
}
